#!/usr/bin/env python3
# -*- coding:utf-8 -*-

import os
import sys

import csv
import sqlite3

import logging
log_format = "%(levelname)s: %(filename)s-%(lineno)d/%(funcName)s: %(message)s"
log_format = "%(levelname)s: %(lineno)d/%(funcName)s: %(message)s"

logging.basicConfig(format=log_format)
log = logging.getLogger("[logdebug]")
log.setLevel(logging.INFO)

class DBServer():
    def __init__(self, path:str, autocommit:bool=False):
        exists = False
        if os.path.exists(path):
            exists = True
        self.dbpath = path
        self.autocommit = autocommit
        self.table_columns = {}

        self.conn = sqlite3.connect(path)
        self.cursor = self.conn.cursor()

        if exists:
            tables = self.query('sqlite_master', 'name', conds='type="table"')
            for row in tables:
                table = row[0]
                columns = self.table_info(table)
                table_col = {}
                for col_desc in columns:
                    cid, name, _type, notnull, dflt_value, pk = col_desc[:]
                    datatype = _type
                    if pk:
                        datatype += ' ' + 'PRIMARY KEY'
                    if notnull:
                        datatype += ' ' + 'NOT NULL'
                    table_col[name] = datatype
                self.table_columns[table] = table_col

    def __del__(self):
        self.commit()
        self.close()

    def close(self):
        self.cursor.close()
        self.conn.close()

    def _exec(self, sqlcmd:str):
        ret = self.exec(sqlcmd)
        if self.autocommit:
            self.commit()
        return ret

    def exec(self, sqlcmd:str):
        try:
            return self.cursor.execute(sqlcmd)
        except Exception as e:
            print('error sql cmd: {}'.format(sqlcmd))
            raise e

    def commit(self):
        self.conn.commit()

    def rollback(self):
        self.conn.rollback()

    def fetchone(self):
        return self.cursor.fetchone()

    def fetchall(self):
        return self.cursor.fetchall()

    def table_info(self, table):
        '''
        cid name type notnull dflt_value pk
        '''
        sql = 'PRAGMA table_info({})'.format(table)
        self._exec(sql)
        return self.fetchall()

    def select(self, table:str, columns='*', conds:str='', ext:str=''):
        if table not in self.table_columns:
            reserved_tables = ('sqlite_master', )
            if table not in reserved_tables:
                raise ValueError('table {} not in db'.format(table))

        sql_columns = columns
        if isinstance(columns, list):
            sql_columns = ', '.join(columns)
        sql = 'SELECT {} FROM {}'.format(sql_columns, table)
        if conds:
            sql += ' WHERE {}'.format(conds)
        if ext:
            sql += ' ' + ext
        return self._exec(sql)

    def query(self, table:str, columns='*', conds:str='', ext:str='', fetchall=True):
        ret = self.select(table, columns, conds, ext)
        if fetchall:
            return self.fetchall()
        return self.fetchone()

    def create_table_list(self, table:str, columns:list):
        '''
        columns = [('id', 'int'), ('val', 'TEXT'), ]
        '''
        if table in self.table_columns:
            raise ValueError()

        table_col = {}
        for col in columns:
            key = col[0]
            table_col[key] = ' '.join(col[1:])

        self.table_columns[table] = table_col

        sql = 'create table {} '.format(table)
        sql += '('
        sql += ',\n'.join([' '.join(col) for col in columns])
        sql += ')'
        return self._exec(sql)

    def create_table_dict(self, table:str, columns:dict):
        '''
        columns = {
            'id': 'int',
            'val': 'TEXT',
        }
        '''
        if table in self.table_columns:
            raise ValueError()
        self.table_columns[table] = columns

        sql = 'create table {} '.format(table)
        sql += '('
        sql += ',\n'.join([name + ' '.join(col) for name, col in columns.items()])
        sql += ')'
        return self._exec(sql)

    def add_row_dict(self, table:str, row:dict):
        """
        INSERT OR REPLACE INTO table-name (column-name,...) VALUES (column-value,...)
        """
        if table not in self.table_columns:
            raise ValueError('table {} not in db'.format(table))
        sql = 'INSERT OR REPLACE INTO {} '.format(table)
        sql += '('
        sql += ', '.join([name for name in row.keys()])
        sql += ')\n'

        sql += 'VALUES ('
        for val in row.values():
            sql += '"{}", '.format(str(val))
        # notice: ', '
        sql = sql.rstrip(', ')
        sql += ')'
        return self._exec(sql)

    def add_row_list(self, table:str, row:list):
        if table not in self.table_columns:
            raise ValueError('table {} not in db'.format(table))

        columns = tuple(self.table_columns[table].keys())
        limit = min(len(row), len(columns))
        sql = 'INSERT OR REPLACE INTO {} '.format(table)
        sql += '('
        sql += ',\n'.join(columns[:limit])
        sql += ')\n'

        sql += 'VALUES ('
        for val in row[:limit]:
            sql += '"{}", '.format(str(val))
        # notice: ', '
        sql = sql.rstrip(', ')
        sql += ')'
        return self._exec(sql)

    def save_csv(self, fcsv, table, columns='*', conds:str='', ext:str='', sortkey=''):
        if table not in self.table_columns:
            raise ValueError('table {} not in db'.format(table))

        with open(fcsv, 'w') as fd:
            header = tuple(self.table_columns[table].keys())
            if columns != '*':
                if isinstance(columns, str):
                    header = columns.split()
                elif type(columns) in (list, tuple):
                    header = columns

            rows = self.query(table, columns, conds, ext)
            if sortkey:
                index = header.index(sortkey)
                if index >= 0:
                    rows = sorted(rows, key=lambda x:x[index])
            writer = csv.writer(fd)
            writer.writerow(header)
            writer.writerows(rows)

    def load_csv(self, fcsv, table, commit=True):
        if table not in self.table_columns:
            raise ValueError()

        with open(fcsv, 'r') as fd:
            reader = csv.reader(fd)
            header = next(reader)
            for row in reader:
                self.add_row_list(table, row)
            if commit:
                self.commit()
